﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_overtime_preprosess')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_overtime_preprosess

GO

-- =============================================
-- Author:      YFC
-- Create date: 2020-08-07 16:27:33
-- Database:    [scm_main-hysj2]
-- Description: 加班管理-根据排班预先生成加班数据
-- =============================================

CREATE PROCEDURE bd_proc_overtime_preprosess

AS
BEGIN
  SET NOCOUNT ON;

  CREATE TABLE #result
  (
    user_serial BIGINT,
    user_no VARCHAR(30),
    user_name VARCHAR(100),
    dep_id BIGINT,
    dep_name VARCHAR(200),
    schedult_date DATE,
    week_name VARCHAR(100),
    shift_id INT,
    shift_name VARCHAR(50),

    shift_begin INT,
    shift_end INT,
    shift_ot_begin INT,
    shift_ot_end INT,

    repast_start_time INT,
    repast_end_time INT,
    repast_start_time2 INT,
    repast_end_time2 INT,

    ot_begin INT,
    ot_end INT,
    repast_hours INT DEFAULT 0,
    ot_hours INT DEFAULT 0,
    actual_hours INT DEFAULT 0,
    --    ot_swipe_begin  INT,
    --    ot_swipe_end  INT,
    swipe_first INT,
    swipe_last INT,
    work_status INT
  )

  INSERT INTO #result(user_serial, schedult_date, shift_id, shift_name, swipe_first, swipe_last, work_status, week_name)
  SELECT sch.user_serial, shift_date, sch.shift_id, card.shift_name, card.swipe_first, card.swipe_last, work_status, card.card_week
  FROM bd_kq_schedule_detail sch
  INNER JOIN bd_kq_timecard card ON card.user_serial = sch.user_serial AND card.shift_id = sch.shift_id AND card.card_date = sch.shift_date
  WHERE ISNULL(is_have_ot, 0) = 1 AND ISNULL(is_created_ot, 0) = 0

  UPDATE r
  SET shift_begin = bc.sbsj
     ,shift_end = bc.xbsj
     ,repast_start_time = bc.repast_start_time
     ,repast_end_time = bc.repast_end_time
     ,repast_start_time2 = bc.repast_start_time2
     ,repast_end_time2 = bc.repast_end_time2
  FROM #result r
  INNER JOIN bd_view_bc_sd bc ON bc.bc_id = r.shift_id AND bc.lx = 0

  UPDATE r
  SET shift_ot_begin = bc.sbsj
     ,shift_ot_end = bc.xbsj
  FROM #result r
  INNER JOIN bd_view_bc_sd bc ON bc.bc_id = r.shift_id AND bc.lx = 1

  --工作日加班,只取加班时段
  UPDATE r
  SET ot_begin = bc.sbsj
     ,ot_end = bc.xbsj
     ,ot_hours = bc.xbsj - bc.sbsj
  FROM #result r
  INNER JOIN bd_view_bc_sd bc ON bc.bc_id = r.shift_id AND bc.lx = 1
  WHERE r.work_status = 0

  --双休、节假日
  --N+8
  UPDATE #result
  SET ot_begin = ISNULL(shift_ot_begin, 0)
     ,ot_end = shift_end
  WHERE work_status >= 1 AND ISNULL(shift_ot_begin, 0) < ISNULL(shift_begin, 0)

  --8+N
  UPDATE #result
  SET ot_begin = ISNULL(shift_begin, 0)
     ,ot_end = shift_ot_end
  WHERE work_status >= 1 AND ISNULL(shift_ot_end, 0) > ISNULL(shift_end, 0)

  --正常班
  UPDATE #result
  SET ot_begin = ISNULL(shift_begin, 0)
     ,ot_end = shift_end
  WHERE work_status >= 1 AND shift_ot_begin IS NULL

  --就餐时间
  UPDATE #result
  SET repast_hours = (ISNULL(repast_end_time, 0) - ISNULL(repast_start_time, 0)) + (ISNULL(repast_end_time2, 0) - ISNULL(repast_start_time2, 0))

  --应出工时
  UPDATE #result
  SET ot_hours = ABS(DATEDIFF(MINUTE, dbo.bd_fn_minToDateTime(ot_end, schedult_date), dbo.bd_fn_minToDateTime(ot_begin, schedult_date))) - repast_hours
  WHERE work_status >= 1

  UPDATE #result
  SET ot_hours = ABS(DATEDIFF(MINUTE, dbo.bd_fn_minToDateTime(ot_end, schedult_date), dbo.bd_fn_minToDateTime(ot_begin, schedult_date)))
  WHERE work_status = 0

  --实出工时
  UPDATE #result
  SET actual_hours = ABS(DATEDIFF(MINUTE, dbo.bd_fn_minToDateTime(swipe_last, schedult_date), dbo.bd_fn_minToDateTime(swipe_first, schedult_date)))

  UPDATE #result
  SET actual_hours =
    CASE
      WHEN ISNULL(actual_hours, 0) >= ISNULL(ot_hours, 0) THEN
        ot_hours
      WHEN ISNULL(actual_hours, 0) < ISNULL(ot_hours, 0) THEN
        actual_hours
      ELSE
        actual_hours
    END

  UPDATE r
  SET user_no = u.user_no
     ,user_name = u.user_lname
     ,dep_id = u.user_dep
     ,dep_name = u.dep_name
  FROM #result r
  INNER JOIN (
      SELECT
        user_serial, user_no, user_lname, user_dep, dep_name
      FROM bd_view_user_all
    ) u ON u.user_serial = r.user_serial


  MERGE
  INTO bd_over_time_preprosess p
  USING #result s ON p.user_serial = s.user_serial
    AND p.schedult_date = s.schedult_date
  WHEN MATCHED
    THEN UPDATE SET
        p.shift_id = s.shift_id
        , p.ot_begin = s.ot_begin
        , p.ot_end = s.ot_end
        , p.ot_hours = s.ot_hours
        , p.actual_hours = s.actual_hours
        , p.ot_swipe_begin = s.swipe_first
        , p.ot_swipe_end = s.swipe_last
        , p.work_status = s.work_status
  WHEN NOT MATCHED BY TARGET
    THEN INSERT(user_serial, schedult_date, shift_id, ot_begin, ot_end, ot_hours, actual_hours, ot_swipe_begin, ot_swipe_end, work_status)
        VALUES(user_serial, schedult_date, shift_id, ot_begin, ot_end, ot_hours, actual_hours, s.swipe_first, s.swipe_last, work_status)
  ;

  --  SELECT * FROM #result 

  DROP TABLE #result

  SET NOCOUNT OFF;
END